﻿CREATE PROCEDURE [util].[SendSQLAgentMailFailureMsg]
@OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	IF(@OutputDiagnostics = 1)
		PRINT OBJECT_NAME(@@PROCID)

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	DECLARE @ServerMailBox nvarchar(100), @DBAMailBox nvarchar(100)
	EXEC @RC = config.GetMailBoxes @ServerMailBox OUTPUT ,@DBAMailBox OUTPUT
	IF(@RC <> 0)
		GOTO ERROR_HANDLER

	DECLARE @MsgSubject nvarchar(256)
	SET @MsgSubject = 'SQL Agent Mail Test Failed on \\'
		+ @@SERVERNAME
	
	DECLARE @MsgBody nvarchar(512)
	SET @MsgBody = 'SQL Agent Mail on Server '
		+ @@SERVERNAME
		+ ' is not working. Restart the SQL Server Agent Service.'
		+ (CASE
			WHEN (SERVERPROPERTY('IsClustered') = 1) THEN
				CHAR(13)
				+ 'SQL Server Services must be re-started via the Cluster Administrator.'
			ELSE N''
		END)

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT N'	@ServerMailBox = N''' + ISNULL(@ServerMailBox,N'NULL') + ''''
			PRINT N'	@DBAMailBox = N''' + ISNULL(@DBAMailBox,N'NULL') + ''''
			PRINT N'	@MsgSubject = N''' + ISNULL(@MsgSubject,N'NULL') + ''''
			PRINT N'	@MsgBody = N''' + ISNULL(@MsgBody,N'NULL') + ''''
		END

	EXEC @RC = util.SendCDOMail @ServerMailBox
		,@DBAMailBox
		,@MsgSubject
		,@MsgBody
	IF(@RC <> 0)
		GOTO ERROR_HANDLER

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

	RAISERROR('Failed to send CDO message to DBA',16,1)

EXIT_PROC:
	RETURN(@RC)
END